//Originally written by David Neal
//I modified it to fit the providers for SmartCodeGenerator.
//This modified version is written by Shahed Khan, shahed.khan@gmail.com

// ====================================================================
// Description: MySQL Schema Provider for CodeSmith 3.x
//      Author: David Neal -- www.ChristianASP.NET
//     Created: 2005-11-27
//
//              Tested with CodeSmith 3.1.4 Build 481 
//              and MySQL Connector/Net 1.0.7
//
// INSTRUCTIONS
// ------------
// Compile and copy assembly to 
//   <Program Files>/CodeSmith/v3.0/SchemaProviders
// ====================================================================

#define SHOWKEYS

using System;
using System.Collections;
using System.Data;
using System.Text;
using System.Text.RegularExpressions;
using MySql.Data.MySqlClient;
using SmartCodeGen.Providers;
using System.Collections.Specialized;

namespace SmartCodeGen.ImplementedProviders
{
	/// <summary>
	/// MySQL Schema Provider 	/// 
    /// Originally Written by: David Neal -- www.ChristianASP.NET
    /// I modified it according to my need to fit to SmartCodeGenerator
    /// Shahed Khan
	/// </summary>
	public class MySqlDBSchemaProvider : DBSchemaProvider
	{
		#region DbSchemaProvider Members

		#region public string GetName

		/// <summary>
		/// Gets the name of this schema provider.
		/// </summary>
		/// <value></value>
		public override string GetName()
		{
			return "MySql DB Schema Provider"; 
		}

		#endregion

		#region public string GetDescription

		/// <summary>
		/// Gets the description for this schema provider.
		/// </summary>
		/// <value></value>
        public override string GetDescription( )
		{
			return "MySQL Schema Provider for SmartCodeGenerator, originally written by David Neal - http://www.ChristianASP.NET, later modified by Shahed Khan."; 
        }
		

		#endregion

		#region public string GetDatabaseName(DatabaseSchema database)

		/// <summary>
		/// Gets the name of the database.
		/// </summary>
		/// <param name="connectionString">The connection string used to connect to the target database.</param>
		/// <returns>The name of the database</returns>
        public override string GetDatabaseName(DatabaseSchema database)
		{
			// Problem is, INFORMATION_SCHEMA appears to retrieve information for
			// every database instead of just the current database
			// So, we'll need to parse the connection string instead of pulling
			// the current database directly

			Regex databaseNameRegex = new Regex(@"Database\W*=\W*(?<database>[^;]*)", RegexOptions.IgnoreCase);
			Match databaseNameMatch;

			if ((databaseNameMatch = databaseNameRegex.Match(database.ConnectionString)).Success)
			{
				return databaseNameMatch.Groups["database"].ToString();
			}
			else
			{
				return database.ConnectionString;
			}
		}

		#endregion

        #region public override string GetViewText(ViewSchema view)

        /// <summary>
		/// Gets the definition for a given view.
		/// </summary>
		/// <param name="connectionString">The connection string used to connect to the target database.</param>
		/// <param name="view"></param>
		/// <returns></returns>
        public override string GetViewText(ViewSchema view)
		{
			StringBuilder sb = new StringBuilder();
			string cmdText = string.Format("SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'", view.Database.Name, view.Name);

			MySqlConnection cnx = new MySqlConnection(view.ConnectionString);
			MySqlCommand cmd = new MySqlCommand(cmdText, cnx);
			MySqlDataReader dr = null;
			try
			{
				cnx.Open();
				dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
				while (dr.Read())
				{
					sb.Append(dr.GetString(0));
				}
			}
			catch
			{
				throw;
			}
			finally
			{
				if (dr != null)
					dr.Close();
			}

			return sb.ToString();

		}

		#endregion

		#region public TableSchemaCollection GetTables(DatabaseSchema database)

		/// <summary>
		/// Gets all of the tables available in the database.
		/// </summary>
		/// <param name="connectionString">The connection string used to connect to the target database.</param>
		/// <param name="database"></param>
		/// <returns></returns>
        public override TableSchemaCollection GetTables(DatabaseSchema database)
        {
            return GetTables(database, database.Name);
        }
        public override TableSchemaCollection GetTables(DatabaseSchema database, string databaseName)
		{
			//ArrayList a = new ArrayList();
            TableSchemaCollection a = new TableSchemaCollection();
			TableSchema ts;
			string cmdText = string.Format("SELECT TABLE_NAME, '' OWNER, CREATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{0}' AND TABLE_TYPE = 'BASE TABLE' ORDER BY 1", databaseName);

			MySqlConnection cnx = new MySqlConnection(database.ConnectionString);
			MySqlCommand cmd = new MySqlCommand(cmdText, cnx);
			MySqlDataReader dr = null;
			try
			{
				cnx.Open();
				dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
				while (dr.Read())
				{
					ts = new TableSchema(database, dr.GetString(0), dr.GetString(1), dr.GetDateTime(2));
					a.Add(ts);
				}
			}
			catch
			{
				throw;
			}
			finally
			{
				if (dr != null)
					dr.Close();
			}

			//return (TableSchemaCollection) a.ToArray(typeof (TableSchema));
            return a;
		}

		#endregion

		#region public ViewSchemaCollection GetViews(DatabaseSchema database)

		/// <summary>
		/// Gets all the views available for a given database.
		/// </summary>
		/// <param name="connectionString">The connection string used to connect to the target database.</param>
		/// <param name="database"></param>
		/// <returns></returns>
        public override ViewSchemaCollection GetViews(DatabaseSchema database)
		{
			//ArrayList a = new ArrayList();
            ViewSchemaCollection a = new ViewSchemaCollection();
			ViewSchema vs;
			string cmdText = string.Format("SELECT TABLE_NAME, '' OWNER, CREATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{0}' AND TABLE_TYPE = 'VIEW' ORDER BY 1", database.Name);

			MySqlConnection cnx = new MySqlConnection(database.ConnectionString);
			MySqlCommand cmd = new MySqlCommand(cmdText, cnx);
			MySqlDataReader dr = null;
			try
			{
				cnx.Open();
				dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
				while (dr.Read())
				{
					// MySQL views don't have a create date?
					DateTime dateCreated = (dr.IsDBNull(2) == false) ? dr.GetDateTime(2) : DateTime.MinValue;

					vs = new ViewSchema(database, dr.GetString(0), dr.GetString(1), dateCreated);
					a.Add(vs);
				}
			}
			catch
			{
				throw;
			}
			finally
			{
				if (dr != null)
					dr.Close();
			}

			//return (ViewSchemaCollection) a.ToArray(typeof (ViewSchema));
            return a;

		}

		#endregion

		#region public CommandSchemaCollection GetCommands(DatabaseSchema database)

		/// <summary>
		/// Gets all commands (stored procedures) for the given database.
		/// </summary>
		/// <param name="connectionString">The connection string used to connect to the target database.</param>
		/// <param name="database"></param>
		/// <returns></returns>
        public override CommandSchemaCollection GetCommands(DatabaseSchema database)
		{
			//ArrayList a = new ArrayList();
            CommandSchemaCollection a = new CommandSchemaCollection();
			CommandSchema cs;
			string cmdText = string.Format("SELECT ROUTINE_NAME, '' OWNER, CREATED FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '{0}' AND ROUTINE_TYPE = 'PROCEDURE' ORDER BY 1", database.Name);

			MySqlConnection cnx = new MySqlConnection(database.ConnectionString);
			MySqlCommand cmd = new MySqlCommand(cmdText, cnx);
			MySqlDataReader dr = null;
			try
			{
				cnx.Open();
				dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
				while (dr.Read())
				{
					cs = new CommandSchema(database, dr.GetString(0), dr.GetString(1), dr.GetDateTime(2));
					a.Add(cs);
				}
			}
			catch
			{
				throw;
			}
			finally
			{
				if (dr != null)
					dr.Close();
			}

			//return (CommandSchemaCollection) a.ToArray(typeof (CommandSchema));
            return a;
		}

		#endregion

		#region public DataTable GetTableData(TableSchema table)

		/// <summary>
		/// Gets the data from the given table.
		/// </summary>		
		/// <param name="table"></param>
		/// <returns></returns>
        public override DataTable GetTableData(TableSchema table)
		{
			MySqlConnection cnx = new MySqlConnection(table.ConnectionString);
			MySqlDataAdapter adapter = new MySqlDataAdapter();
			string cmdText = string.Format("SELECT * FROM {0}", table.Name);
			try
			{
				cnx.Open();
				MySqlCommand cmd = new MySqlCommand(cmdText, cnx);
				// cmd.CommandTimeout = 60;
				DataSet ds = new DataSet();
				adapter.SelectCommand = cmd;
				adapter.Fill(ds);

				return ds.Tables[0];
			}
			catch
			{
				throw;
			}
			finally
			{
				if (cnx != null)
					cnx.Close();
			}

		}

		#endregion

		#region public DataTable GetViewData(ViewSchema view)

		/// <summary>
		/// Gets the data from a given view.
		/// </summary>		
		/// <param name="view"></param>
		/// <returns></returns>
        public override DataTable GetViewData(ViewSchema view)
		{
			MySqlConnection cnx = new MySqlConnection(view.ConnectionString);
			MySqlDataAdapter adapter = new MySqlDataAdapter();
			string cmdText = string.Format("SELECT * FROM {0}", view.Name);
			try
			{
				cnx.Open();
				MySqlCommand cmd = new MySqlCommand(cmdText, cnx);
				cmd.CommandTimeout = 60;
				DataSet ds = new DataSet();
				adapter.SelectCommand = cmd;
				adapter.Fill(ds);

				return ds.Tables[0];
			}
			catch
			{
				throw;
			}
			finally
			{
				if (cnx != null)
					cnx.Close();
			}

		}

		#endregion

		#region public ColumnSchemaCollection GetTableColumns(TableSchema table)

		/// <summary>
		/// Gets all columns for a given table.
		/// </summary>
		
		/// <param name="table"></param>
		/// <returns></returns>
        public override ColumnSchemaCollection GetTableColumns(TableSchema table)
		{
			//ArrayList a = new ArrayList();
            ColumnSchemaCollection a = new ColumnSchemaCollection();
			ColumnSchema obj;
			string cmdText = string.Format("SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION,"
				+ " NUMERIC_SCALE, CASE IS_NULLABLE WHEN 'NO' THEN 0 ELSE 1 END IS_NULLABLE, COLUMN_TYPE"
				+ " FROM INFORMATION_SCHEMA.COLUMNS"
				+ " WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'"
				+ " ORDER BY ORDINAL_POSITION", table.Database.Name, table.Name);

			MySqlConnection cnx = new MySqlConnection(table.ConnectionString);
			MySqlCommand cmd = new MySqlCommand(cmdText, cnx);
			MySqlDataReader dr = null;
			try
			{
				cnx.Open();
				dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
				while (dr.Read())
				{
					string name = dr.GetString(0);
					string nativeType = dr.GetString(1);
					long longSize = (dr.IsDBNull(2) == false) ? dr.GetInt64(2) : 0;
					byte precision = (byte) ((dr.IsDBNull(3) == false) ? dr.GetInt32(3) : 0);
					int scale = (dr.IsDBNull(4) == false) ? dr.GetInt32(4) : 0;
					bool isNullable = (dr.IsDBNull(5) == false) ? dr.GetBoolean(5) : false;
					string columnType = dr.GetString(6);

					int size = (longSize < ((long) int.MaxValue)) ? (int) longSize : int.MaxValue;

					bool isUnsigned = (columnType.IndexOf("unsigned") > -1);
					DbType t = GetDbType(nativeType, isUnsigned);

					obj = new ColumnSchema(table, name, t, nativeType, size, precision, scale, isNullable);
					a.Add(obj);
				}
			}
			catch
			{
				throw;
			}
			finally
			{
				if (dr != null)
					dr.Close();
			}

			//return (ColumnSchemaCollection) a.ToArray(typeof (ColumnSchema));
            return a;
		}

		#endregion

		#region public ViewColumnSchemaCollection GetViewColumns(ViewSchema view)

		/// <summary>
		/// Gets the columns for a given view.
		/// </summary>
		/// <param name="connectionString">The connection string used to connect to the target database.</param>
		/// <param name="view"></param>
		/// <returns></returns>
        public override ViewColumnSchemaCollection GetViewColumns(ViewSchema view)
		{
			//ArrayList a = new ArrayList();
            ViewColumnSchemaCollection a = new ViewColumnSchemaCollection();
			ViewColumnSchema obj;
			string cmdText = string.Format("SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION,"
				+ " NUMERIC_SCALE, CASE IS_NULLABLE WHEN 'NO' THEN 0 ELSE 1 END IS_NULLABLE, COLUMN_TYPE"
				+ " FROM INFORMATION_SCHEMA.COLUMNS "
				+ "WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'"
				+ "ORDER BY ORDINAL_POSITION", view.Database.Name, view.Name);

			MySqlConnection cnx = new MySqlConnection(view.ConnectionString);
			MySqlCommand cmd = new MySqlCommand(cmdText, cnx);
			MySqlDataReader dr = null;
			try
			{
				cnx.Open();
				dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
				while (dr.Read())
				{
					string name = dr.GetString(0);
					string nativeType = dr.GetString(1);
                    long longSize = (dr.IsDBNull(2) == false) ? dr.GetInt64(2) : 0;
                    byte precision = (byte)((dr.IsDBNull(3) == false) ? dr.GetInt32(3) : 0);
                    int scale = (dr.IsDBNull(4) == false) ? dr.GetInt32(4) : 0;
                    bool isNullable = (dr.IsDBNull(5) == false) ? dr.GetBoolean(5) : false;
                    string columnType = dr.GetString(6);

                    int size = (longSize < ((long)int.MaxValue)) ? (int)longSize : int.MaxValue;

					bool isUnsigned = (columnType.IndexOf("unsigned") > -1);
					DbType t = GetDbType(nativeType, isUnsigned);

					obj = new ViewColumnSchema(view, name, t, nativeType, size, precision, scale, isNullable);
					a.Add(obj);
				}
			}
			catch
			{
				throw;
			}
			finally
			{
				if (dr != null)
					dr.Close();
			}

			//return (ViewColumnSchemaCollection) a.ToArray(typeof (ViewColumnSchema));
            return a;
		}

		#endregion

		#region public PrimaryKeySchema GetTablePrimaryKey(string connectionString, TableSchema table)

		/// <summary>
		/// Gets the primary key for a given table.
		/// </summary>
		/// <param name="connectionString">The connection string used to connect to the target database.</param>
		/// <param name="table"></param>
		/// <returns></returns>
        public override PrimaryKeySchema GetTablePrimaryKey(TableSchema table)
		{
#if SHOWKEYS
            PrimaryKeySchema pks = null;

            string cmdText = string.Format("SHOW KEYS FROM {0}.{1} WHERE Key_name = 'PRIMARY'", table.Database.Name, table.Name);

            MySqlConnection cnx = null;
            MySqlCommand cmd = null;
            try
            {
                cnx = new MySqlConnection(table.ConnectionString);
                cnx.Open();
                cmd = new MySqlCommand(cmdText, cnx);
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                DataSet ds = new DataSet();
                adapter.SelectCommand = cmd;
                adapter.Fill(ds);

                if (ds.Tables[0].Rows.Count > 0)
                {
                    string constraintName = ds.Tables[0].Rows[0]["Key_name"].ToString();
                    string[] members = new string[ds.Tables[0].Rows.Count];

                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        members[i] = ds.Tables[0].Rows[i]["Column_name"].ToString();
                    }

                    pks = new PrimaryKeySchema(table, constraintName, members);
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (cnx != null)
                    cnx.Close();
            }

            return pks;
#else
			PrimaryKeySchema pks = null;

			string cmdText = string.Format("SELECT t1.CONSTRAINT_NAME, t1.COLUMN_NAME"
				+ " FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1"
				+ "  INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t2"
				+ "  ON t2.TABLE_SCHEMA = t1.TABLE_SCHEMA"
				+ "  AND t2.TABLE_NAME = t1.TABLE_NAME"
				+ "  AND t2.CONSTRAINT_NAME = t1.CONSTRAINT_NAME"
				+ " WHERE t1.TABLE_SCHEMA = '{0}' AND t1.TABLE_NAME = '{1}'"
				+ " AND t2.CONSTRAINT_TYPE = 'PRIMARY KEY'"
				+ " ORDER BY t1.ORDINAL_POSITION", table.Database.Name, table.Name);

			MySqlConnection cnx = null;
			MySqlCommand cmd = null; 
			try
			{
				cnx = new MySqlConnection(table.ConnectionString);
				cnx.Open();
				cmd = new MySqlCommand(cmdText, cnx);
				MySqlDataAdapter adapter = new MySqlDataAdapter();
				DataSet ds = new DataSet();
				adapter.SelectCommand = cmd;
				adapter.Fill(ds);

				if (ds.Tables[0].Rows.Count > 0)
				{
					string constraintName = ds.Tables[0].Rows[0]["CONSTRAINT_NAME"].ToString();
					string[] members = new string[ds.Tables[0].Rows.Count];

					for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
					{
						members[i] = ds.Tables[0].Rows[i]["COLUMN_NAME"].ToString();
					}

					pks = new PrimaryKeySchema(table, constraintName, members);
				}
			}
			catch
			{
				throw;
			}
			finally
			{
				if (cnx != null)
					cnx.Close();
			}

			return pks;
#endif
		}

		#endregion

		#region public TableKeySchemaCollection GetTableKeys(TableSchema table)

		/// <summary>
		/// Gets all of the table keys for a given table.
		/// </summary>
		/// <param name="connectionString">The connection string used to connect to the target database.</param>
		/// <param name="table"></param>
		/// <returns></returns>
        public override TableKeySchemaCollection GetTableKeys(TableSchema table)
		{
			//ArrayList a = new ArrayList();
            TableKeySchemaCollection a = new TableKeySchemaCollection();

			GetMyTableKeys(table, a);
			GetOthersTableKeys(table, a);

			//return (TableKeySchemaCollection) a.ToArray(typeof (TableKeySchema));
            return a;
		}

        private void GetMyTableKeys(TableSchema table, TableKeySchemaCollection a)
		{
			TableKeySchema tblSchema;
			string cmdText = string.Format("SELECT CONSTRAINT_NAME"
				+ " FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t1"
				+ " WHERE t1.TABLE_SCHEMA = '{0}' AND t1.TABLE_NAME = '{1}'"
				+ "  AND CONSTRAINT_TYPE = 'FOREIGN KEY';"

				+ " SELECT t1.CONSTRAINT_NAME, t1.COLUMN_NAME, t1.POSITION_IN_UNIQUE_CONSTRAINT,"
				+ "  t1.REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME"
				+ " FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1"
				+ "  INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t2"
				+ "  ON t2.TABLE_SCHEMA = t1.TABLE_SCHEMA"
				+ "  AND t2.TABLE_NAME = t1.TABLE_NAME"
				+ "  AND t2.CONSTRAINT_NAME = t1.CONSTRAINT_NAME"
				+ " WHERE t1.TABLE_SCHEMA = '{0}' AND t1.TABLE_NAME = '{1}'"
				+ "  AND t2.CONSTRAINT_TYPE = 'FOREIGN KEY'"
				+ " ORDER BY t1.CONSTRAINT_NAME, t1.POSITION_IN_UNIQUE_CONSTRAINT", table.Database.Name, table.Name);

			MySqlConnection cnx = null;
			MySqlCommand cmd = null; 
			try
			{
				cnx = new MySqlConnection(table.ConnectionString);
				cnx.Open();
				cmd = new MySqlCommand(cmdText, cnx);
				MySqlDataAdapter adapter = new MySqlDataAdapter();
				DataSet ds = new DataSet();
				adapter.SelectCommand = cmd;
				adapter.Fill(ds);

				if (ds.Tables[0].Rows.Count > 0)
				{
					// Add constraint to keys relationship
					ds.Relations.Add("Contraint_to_Keys", ds.Tables[0].Columns["CONSTRAINT_NAME"], ds.Tables[1].Columns["CONSTRAINT_NAME"]);

					foreach(DataRow constraintRow in ds.Tables[0].Rows)
					{
						string name = constraintRow["CONSTRAINT_NAME"].ToString();

						// Get the keys
						DataRow[] keys = constraintRow.GetChildRows("Contraint_to_Keys");

						string[] primaryKeys = new string[keys.Length];
						string[] foreignKeys = new string[keys.Length];
						string fkTable = table.Name;
						string pkTable = keys[0]["REFERENCED_TABLE_NAME"].ToString();

						for(int i = 0; i < keys.Length; i++)
						{
							foreignKeys[i] = keys[i]["COLUMN_NAME"].ToString();
							primaryKeys[i] = keys[i]["REFERENCED_COLUMN_NAME"].ToString();
						}

						tblSchema = new TableKeySchema(table.Database, name, foreignKeys, fkTable, primaryKeys, pkTable );

						a.Add(tblSchema);
					}
				}
			}
			catch
			{
				throw;
			}
			finally
			{
				if (cnx != null) cnx.Close();
			}
		}

		/// <summary>
		/// DRS 2006-01-24 : GetTableKeys must return both the foreign keys contained within the
		///		given table AND the foreign keys that point at the given table from other tables.  I've
		///		added this method to find those keys.  I tried as best I could to stick to the same
		///		coding structure as was used in the original GetTableKeys (which can now be found in
		///		GetMyTableKeys).
		/// </summary>
        private void GetOthersTableKeys(TableSchema table, TableKeySchemaCollection a)
		{
			TableKeySchema tblSchema;
			string cmdText = string.Format("SELECT DISTINCT CONSTRAINT_NAME"
				+ " FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1"
				+ " WHERE t1.TABLE_SCHEMA = '{0}' AND t1.REFERENCED_TABLE_NAME = '{1}';"

				+ " SELECT t1.CONSTRAINT_NAME, t1.TABLE_NAME, t1.COLUMN_NAME, t1.POSITION_IN_UNIQUE_CONSTRAINT,"
				+ "  t1.REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME"
				+ " FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1"
				+ "  INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t2"
				+ "  ON t2.TABLE_SCHEMA = t1.TABLE_SCHEMA"
				+ "  AND t2.TABLE_NAME = t1.TABLE_NAME"
				+ "  AND t2.CONSTRAINT_NAME = t1.CONSTRAINT_NAME"
				+ " WHERE t1.TABLE_SCHEMA = '{0}' AND t1.REFERENCED_TABLE_NAME = '{1}'"
				+ "  AND t2.CONSTRAINT_TYPE = 'FOREIGN KEY'"
				+ " ORDER BY t1.CONSTRAINT_NAME, t1.POSITION_IN_UNIQUE_CONSTRAINT", table.Database.Name, table.Name);

			MySqlConnection cnx = null;
			MySqlCommand cmd = null; 
			try
			{
				cnx = new MySqlConnection(table.ConnectionString);
				cnx.Open();
				cmd = new MySqlCommand(cmdText, cnx);
				MySqlDataAdapter adapter = new MySqlDataAdapter();
				DataSet ds = new DataSet();
				adapter.SelectCommand = cmd;
				adapter.Fill(ds);

				if (ds.Tables[0].Rows.Count > 0)
				{
					// Add constraint to keys relationship
					ds.Relations.Add("Contraint_to_Keys", ds.Tables[0].Columns["CONSTRAINT_NAME"], ds.Tables[1].Columns["CONSTRAINT_NAME"]);

					foreach(DataRow constraintRow in ds.Tables[0].Rows)
					{
						string name = constraintRow["CONSTRAINT_NAME"].ToString();

						// Get the keys
						DataRow[] keys = constraintRow.GetChildRows("Contraint_to_Keys");

						string[] primaryKeys = new string[keys.Length];
						string[] foreignKeys = new string[keys.Length];
						string fkTable = keys[0]["TABLE_NAME"].ToString();
						string pkTable = keys[0]["REFERENCED_TABLE_NAME"].ToString();

						for(int i = 0; i < keys.Length; i++)
						{
							foreignKeys[i] = keys[i]["COLUMN_NAME"].ToString();
							primaryKeys[i] = keys[i]["REFERENCED_COLUMN_NAME"].ToString();
						}

						tblSchema = new TableKeySchema(table.Database, name, foreignKeys, fkTable, primaryKeys, pkTable );

						a.Add(tblSchema);
					}
				}
			}
			catch
			{
				throw;
			}
			finally
			{
				if (cnx != null) cnx.Close();
			}
		}

		#endregion

		#region public IndexSchemaCollection GetTableIndexes(TableSchema table)

		/// <summary>
		/// Gats all of the indexes for a given table.
		/// </summary>
		
		/// <param name="table"></param>
		/// <returns></returns>
        public override IndexSchemaCollection GetTableIndexes(TableSchema table)
		{
			//ArrayList a = new ArrayList();
            IndexSchemaCollection a = new IndexSchemaCollection();
			IndexSchema ndxSchema;
			string cmdText = string.Format("SELECT INDEX_NAME, COUNT(*) AS COLUMN_COUNT, MAX(NON_UNIQUE) NON_UNIQUE,"
				+ " CASE INDEX_NAME WHEN 'PRIMARY' THEN 1 ELSE 0 END IS_PRIMARY"
				+ " FROM INFORMATION_SCHEMA.STATISTICS"
				+ " WHERE  TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'"
				+ " GROUP BY INDEX_NAME"
				+ " ORDER BY INDEX_NAME;"

				+ " SELECT INDEX_NAME, COLUMN_NAME"
				+ " FROM INFORMATION_SCHEMA.STATISTICS"
				+ " WHERE  TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'"
				+ " ORDER BY INDEX_NAME, SEQ_IN_INDEX;", table.Database.Name, table.Name);

			MySqlConnection cnx = null;
			MySqlCommand cmd = null; 
			try
			{
				cnx = new MySqlConnection(table.ConnectionString);
				cnx.Open();
				cmd = new MySqlCommand(cmdText, cnx);
				MySqlDataAdapter adapter = new MySqlDataAdapter();
				DataSet ds = new DataSet();
				adapter.SelectCommand = cmd;
				adapter.Fill(ds);

				if (ds.Tables[0].Rows.Count > 0)
				{
					// Add constraint to keys relationship
					ds.Relations.Add("INDEX_to_COLUMNS", ds.Tables[0].Columns["INDEX_NAME"], ds.Tables[1].Columns["INDEX_NAME"]);

					foreach(DataRow ndxRow in ds.Tables[0].Rows)
					{
						string name = ndxRow["INDEX_NAME"].ToString();
						//bool isPrimaryKey = (((long) ndxRow["IS_PRIMARY"]) == 1);
						//bool isUnique = !(((long) ndxRow["NON_UNIQUE"]) == 1);
                        bool isPrimaryKey = (((int) ndxRow["IS_PRIMARY"]) == 1);
                        bool isUnique = !(((long) ndxRow["NON_UNIQUE"]) == 1);
						
                        bool isClustered = isPrimaryKey; // Primary Key indexes are automatically clustered in MySQL

						// Get the columns
						DataRow[] cols = ndxRow.GetChildRows("INDEX_to_COLUMNS");

						string[] memberColumns = new string[cols.Length];
						
						for(int i = 0; i < cols.Length; i++)
						{
							memberColumns[i] = cols[i]["COLUMN_NAME"].ToString();
						}

						ndxSchema = new IndexSchema(table, name, isPrimaryKey, isUnique, isClustered, memberColumns);

						a.Add(ndxSchema);
					}
				}
			}
			catch
			{
				throw;
			}
			finally
			{
				if (cnx != null)
					cnx.Close();
			}

			//return (IndexSchemaCollection) a.ToArray(typeof (IndexSchema));
            return a;
		}

		#endregion

		#region public string GetCommandText(CommandSchema command)

		/// <summary>
		/// Gets the definition for a given command.
		/// </summary>
		/// <param name="connectionString">The connection string used to connect to the target database.</param>
		/// <param name="command"></param>
		/// <returns></returns>
        public override string GetCommandText(CommandSchema command)
		{
			StringBuilder sb = new StringBuilder();
			string cmdText = string.Format("SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '{0}' AND ROUTINE_NAME = '{1}'", command.Database.Name, command.Name);

			MySqlConnection cnx = new MySqlConnection(command.ConnectionString);
			MySqlCommand cmd = new MySqlCommand(cmdText, cnx);
			MySqlDataReader dr = null;
			try
			{
				cnx.Open();
				dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
				while (dr.Read())
				{
					sb.Append(dr.GetString(0));
				}
			}
			catch
			{
				throw;
			}
			finally
			{
				if (dr != null)
					dr.Close();
			}

			return sb.ToString();
		}

		#endregion

		/// <summary>
		/// Gets the parameters for a given command.
		/// </summary>
		
		/// <param name="command"></param>
		/// <returns></returns>
        public override ParameterSchemaCollection GetCommandParameters(CommandSchema command)
		{
			// MySQL does not yet implement INFORMATION_SCHEMA.PARAMETERS
			// MySQL Connector/Net 1.0.7 is supposed to support DeriveParameters()
			// However, in my testing there appears to be a bug (throws a NULL reference exception)
			// This method will be unsupported until a subsequent release of DeriverParameters()
			// is working.

			throw new NotSupportedException("GetCommandParameters() is not supported in this release.");

			/*
			ArrayList a = new ArrayList();
			ParameterSchema ps;
			MySqlConnection cnx = null;
			MySqlCommand cmd = null;
			try
			{
				cnx = new MySqlConnection(connectionString);

				cmd = new MySqlCommand(command.Name, cnx);
				cmd.CommandType = CommandType.StoredProcedure;

				cnx.Open();
				MySqlCommandBuilder.DeriveParameters(cmd);
				cnx.Close();

				foreach(MySqlParameter param in cmd.Parameters)
				{
					ps = new ParameterSchema(command, param.ParameterName, param.Direction, param.DbType, 
						param.MySqlDbType.ToString(), param.Size, param.Precision, param.Scale, param.IsNullable);

					a.Add(ps);
				}

			}
			catch
			{
				throw;
			}
			finally
			{
				if (cnx != null)
					cnx.Close();
			}

			return (ParameterSchemaCollection) a.ToArray(typeof (ParameterSchema));
			*/
		}

		/// <summary>
		/// Gets schema information about the results of a given command.
		/// </summary>		
		/// <param name="command"></param>
		/// <returns></returns>
        public override CommandResultSchemaCollection GetCommandResultSchemas(CommandSchema command)
		{
			// TODO:  Add MySQLSchemaProvider.GetCommandResultSchemas implementation
			throw new NotSupportedException("GetCommandResultSchemas() is not supported in this release.");
		}

		/// <summary>
		/// Gets the extended properties for a given schema object.
		/// </summary>
		/// <param name="connectionString">The connection string used to connect to the target database.</param>
		/// <param name="schemaObject"></param>
		/// <returns></returns>
        public override ExtendedPropertyCollection GetExtendedProperties(SchemaBase schemaObject)
		{
			// TODO:  Add MySQLSchemaProvider.GetExtendedProperties implementation

			//ArrayList a = new ArrayList();
            ExtendedPropertyCollection a = new ExtendedPropertyCollection();

			if (schemaObject is ColumnSchema)
			{
				ExtendedProperty exProp;
				ColumnSchema cs = schemaObject as ColumnSchema;

				string cmdText = string.Format(@"SELECT EXTRA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '{0}'
  AND TABLE_NAME = '{1}'
  AND COLUMN_NAME = '{2}'", cs.Table.Database.Name, cs.Table.Name, cs.Name);

				MySqlConnection cnx = new MySqlConnection(schemaObject.ConnectionString);
				MySqlCommand cmd = new MySqlCommand(cmdText, cnx);
				MySqlDataReader dr = null;
				try
				{
					cnx.Open();
					dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
					if (dr.Read())
					{
						string extra = dr.GetString(0).ToLower();
						bool isIdentity = (extra.IndexOf("auto_increment") > -1);
						exProp = new ExtendedProperty("CS_IsIdentity", isIdentity, cs.DataType);
						a.Add(exProp);
						if (isIdentity)
						{
							/*
							MySQL auto_increment doesn't work exactly like SQL Server's IDENTITY
							I believe that auto_increment is equivalent to IDENTITY(1, 1)
							However, auto_increment behaves differently from IDENTITY when used
							with multi-column primary keys.  See the MySQL Reference Manual for details.
							*/
							exProp = new ExtendedProperty("CS_IdentitySeed", 1, cs.DataType);
							a.Add(exProp);

							exProp = new ExtendedProperty("CS_IdentityIncrement", 1, cs.DataType);
							a.Add(exProp);
						}
					}
				}
				catch
				{
					throw;
				}
				finally
				{
					if (dr != null)
						dr.Close();
				}

			}

			//return (ExtendedPropertyCollection) a.ToArray(typeof (ExtendedProperty));
            return a;
		}

		#endregion

		#region private DbType GetDbType(string type, bool isUnsigned)

		/// <summary>
		/// Gets the data type of the given native type.
		/// </summary>
		/// <param name="type">The native data type.</param>
		/// <param name="isUnsigned">if set to <c>true</c> [is unsigned].</param>
		/// <returns></returns>
        private DbType GetDbType(string type, bool isUnsigned)
		{
			string t = type.ToLower();
			switch (t)
			{
				case "bit":
					return DbType.UInt16;
				case "tinyint":
					return (isUnsigned) ? DbType.Byte : DbType.SByte;
				case "smallint":
					return (isUnsigned) ? DbType.UInt16 : DbType.Int16;
				case "mediumint":
				case "int":
					return (isUnsigned) ? DbType.UInt32 : DbType.Int32;
				case "bigint":
					return (isUnsigned) ? DbType.UInt64 : DbType.Int64;
				case "float":
					return DbType.Single;
				case "double":
					return DbType.Double;
				case "decimal":
					return DbType.Decimal;
				case "date":
					return DbType.Date;
				case "datetime":
					return DbType.DateTime;
				case "timestamp":
					return DbType.DateTime;
				case "time":
					return DbType.Time;

				case "text":
				case "char":
				case "varchar":
                case "ntext":
                case "nchar":
                case "nvarchar":
                case "tinytext":
                case "mediumtext":
                case "longtext":
					return DbType.String;

				case "blob":
                case "tinyblob":
                case "mediumblob":
                case "longblob":
					return DbType.Binary;

				default:
					return DbType.Object;
			}
		}

		#endregion

        #region Provider specific behaviors
        public override void Initialize(string name, NameValueCollection configValue)
        {

        }

        public override string Name
        {
            get
            {
                return null;
            }
        }
        #endregion
	}
}